﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using PPI.Entities;
using PPI.Entities.Inspections;
using PPI.Entities.Lookup;
using PPI.Mobile.Logic;
using VMind.Global;

namespace PPI.Client.Logic.Inspection
{
    public class LInspectionResult
    {

        #region QueryMobile

        public static Int32 CountMobileInspectionsResults(string connectionString)
        {
            StringBuilder _sql = new StringBuilder();
            _sql.AppendLine("select count(*) FROM tb_UtilityInspectionResults");
            return (Int32)LMobileDatabase.ExecuteScalar(_sql.ToString(), connectionString);


        }

        public static List<EInspectionResult> GetMobileInspectionResults(string connectionString)
        {
            StringBuilder _sql = new StringBuilder();
            
            #region Query
            _sql.AppendLine("SELECT ");
            _sql.AppendLine("   U.UtilityInspectionResultId, ");
            _sql.AppendLine("   U.UtilityInspectionFk, ");
            _sql.AppendLine("   U.InspectionResultServerID, ");
            _sql.AppendLine("   U.InspectionServerID, ");
            _sql.AppendLine("   U.Comments, ");
            _sql.AppendLine("   U.LastInspection, ");
            _sql.AppendLine("   U.InspectionTypeFk, ");
            _sql.AppendLine("   I.Name as InspectionType_Name, ");
            _sql.AppendLine("   U.ResultTypeFk, ");
            _sql.AppendLine("   R.Name as ResultType_Name, ");
            _sql.AppendLine("   U.isElectrical as bElectrical ");
            _sql.AppendLine(" FROM tb_UtilityInspectionResults U ");
            _sql.AppendLine("   LEFT JOIN tb_InspectionTypes I ON U.InspectionTypeFk = I.InspectionTypeId");
            _sql.AppendLine("   LEFT JOIN tb_ResultTypes R ON U.ResultTypeFk= R.ResultTypeId ");
            //_sql.AppendLine(" Where U.UtilityInspectionResultId = @UtilityInspectionResultId");



            #endregion

            DataTable dt = LMobileDatabase.ExecuteQueryDataset(_sql.ToString(), connectionString).Tables[0];

            List<EInspectionResult> _ListResults = new List<EInspectionResult>();

            foreach (DataRow dr in dt.Rows)
            {
                EInspectionResult _result = new EInspectionResult();
                _result.Id = ConvertHelper.ToInt32(dr["UtilityInspectionResultId"]);
                _result.SeverId = ConvertHelper.ToInt32(dr["InspectionResultServerID"]);
                _result.Inspection = LInspection.SelectData(connectionString, ConvertHelper.ToInt32(dr["UtilityInspectionFk"]));
                _result.InspectionType = new EInspectionTypes(ConvertHelper.ToInt32(dr["InspectionTypeFk"]), ConvertHelper.Tostring(dr["InspectionType_Name"]));
                _result.LastInspection = ConvertHelper.Tostring(dr["LastInspection"]);
                _result.Resultype = new EResultType(ConvertHelper.ToInt32(dr["ResultTypeFk"]), ConvertHelper.Tostring(dr["ResultType_Name"]));
                _result.Notes = LNote.GetMobileByResultId(connectionString,_result.Id);
                _result.IsElectrical = ConvertHelper.Tobool(dr["bElectrical"]);
                _result.Comments = dr["Comments"].ToString();
                _result.Inspection.Result = _result;
                _ListResults.Add(_result);

            }
            return _ListResults;
        }

        public static void UpdateUploadedData(EInspectionResult inspectionResult, string connectionString)
        {
            StringBuilder _query = new StringBuilder();

            _query.AppendLine("update tb_UtilityInspectionResults set InspectionResultServerID =" + inspectionResult.SeverId +
                  ",UploadedDate = " + DateTime.Now.ToString("yyyy-MM-dd") + " where UtilityInspectionResultId =" + inspectionResult.Id + "   ");
            LMobileDatabase.ExecuteQuery(_query.ToString(), connectionString);

            _query = new StringBuilder();
            _query.AppendLine("Update tb_Images set InspectionResultServerID =" + inspectionResult.SeverId + " where UtilityInspectionResultFk =" + inspectionResult.Id + " ");
            LMobileDatabase.ExecuteQuery(_query.ToString(), connectionString);

        }

        #region CRUD

        public static Int32 SaveMobile(EInspectionResult inspectionResult,string connectionString)
        {
            Int32 returnId = inspectionResult.Id;
            if (inspectionResult.Id != Int32.MinValue)
                UpdateMobile(inspectionResult, connectionString);
            return returnId;

        }

        private static void UpdateMobile(EInspectionResult result, string connectionString)
        {
            StringBuilder _Query  = new StringBuilder();
            List<SqlParameter> _ListParameters = new List<SqlParameter>(); 
            #region Query

            _Query.Append("Update tb_UtilityInspectionResults SET ");
            _Query.Append("     UtilityInspectionFk = @UtilityInspectionFk,");
            _Query.Append("     InspectionTypeFk = @InspectionTypeFk,");
            _Query.Append("     ResultTypeFk = @ResultTypeFk,");
            _Query.Append("     LastInspection = @LastInspection, ");
            _Query.Append("     Comments = @Comments,");
            _Query.Append("     isElectrical = @isElectrical");
            _Query.Append(" Where UtilityInspectionResultId = @UtilityInspectionResultId");

            #endregion

            #region Parameters
            _ListParameters.Add(DBHelper.CreateIntParameter("@UtilityInspectionResultId", ParameterDirection.Input, false, result.Id));
            _ListParameters.Add(DBHelper.CreateIntParameter("@UtilityInspectionFk", ParameterDirection.Input, false, result.Inspection.Id));
            _ListParameters.Add(DBHelper.CreateIntParameter("@ResultTypeFk", ParameterDirection.Input, false, result.Resultype.Id));
            _ListParameters.Add(DBHelper.CreateIntParameter("@InspectionTypeFk", ParameterDirection.Input, false, result.InspectionType.Id));
            _ListParameters.Add(DBHelper.CreateBitParameter("@isElectrical", ParameterDirection.Input, false, result.IsElectrical));


            if (!string.IsNullOrEmpty(result.LastInspection))
                _ListParameters.Add(DBHelper.CreateNVarCharParameter("@LastInspection", 2, ParameterDirection.Input, false, result.LastInspection));
            else
                _ListParameters.Add(DBHelper.CreateNVarCharParameter("@LastInspection", 2, ParameterDirection.Input, true, DBNull.Value));

            if (!string.IsNullOrEmpty(result.Comments))
                _ListParameters.Add(DBHelper.CreateNVarCharParameter("@Comments", 512, ParameterDirection.Input, false, result.Comments));
            else
                _ListParameters.Add(DBHelper.CreateNVarCharParameter("@Comments", 512, ParameterDirection.Input, true, DBNull.Value));

            


            #endregion

            try
            {
                LMobileDatabase.ExecuteSingleQueryWhithParameters(_Query.ToString(), connectionString, _ListParameters.ToArray());

                LInspection.SaveMobile(result.Inspection, connectionString);

                foreach (EInspectionImage i in result.Inspection.Images)
                {
                    LInspectionImage.SaveMobile(i,connectionString);
                }
                
                
                LNote.DeleteMobile(result, connectionString);
                foreach (ENote n in result.Notes)
                {
                    LNote.InsertMobile(n, result.Id, connectionString);
                }

            }
            catch (Exception ex)
            {
                
                throw ex;
            }



        }

        public static void InsertMobileProject(EInspectionResult result, string connectionString)
        {
            StringBuilder _Query = new StringBuilder();
            List<SqlParameter> _ListParameters = new List<SqlParameter>();
            #region Query

            _Query.Append("insert into tb_UtilityInspectionResults ");
            _Query.Append("     (");
            _Query.Append("         UtilityInspectionResultId,");
            _Query.Append("         UtilityInspectionFk,");
            _Query.Append("         InspectionTypeFk,");
            _Query.Append("         ResultTypeFk,");
            _Query.Append("         LastInspection,");
            _Query.Append("         Comments,");
            _Query.Append("         bElectrical");
            _Query.Append("     )Values(");
            _Query.Append("         @UtilityInspectionResultId,");
            _Query.Append("         @UtilityInspectionFk,");
            _Query.Append("         @InspectionTypeFk,");
            _Query.Append("         @ResultTypeFk,");
            _Query.Append("         @LastInspection,");
            _Query.Append("         @Comments,");
            _Query.Append("         @bElectrical");
            _Query.Append("     )");

            #endregion

            #region Parameters
            _ListParameters.Add(DBHelper.CreateIntParameter("@UtilityInspectionResultId", ParameterDirection.Input, false, result.Id));
            _ListParameters.Add(DBHelper.CreateIntParameter("@UtilityInspectionFk", ParameterDirection.Input, false, result.Inspection.Id));
            _ListParameters.Add(DBHelper.CreateIntParameter("@ResultTypeFk", ParameterDirection.Input, false, result.Resultype.Id));
            _ListParameters.Add(DBHelper.CreateIntParameter("@InspectionTypeFk", ParameterDirection.Input, false, result.InspectionType.Id));
            _ListParameters.Add(DBHelper.CreateIntParameter("@bElectrical", ParameterDirection.Input, false, result.IsElectrical));


            if (!string.IsNullOrEmpty(result.LastInspection))
                _ListParameters.Add(DBHelper.CreateNVarCharParameter("@LastInspection", 2, ParameterDirection.Input, false, result.LastInspection));
            else
                _ListParameters.Add(DBHelper.CreateNVarCharParameter("@LastInspection", 2, ParameterDirection.Input, true, DBNull.Value));

            if (!string.IsNullOrEmpty(result.Comments))
                _ListParameters.Add(DBHelper.CreateNVarCharParameter("@Comments", 512, ParameterDirection.Input, false, result.Comments));
            else
                _ListParameters.Add(DBHelper.CreateNVarCharParameter("@Comments", 512, ParameterDirection.Input, true, DBNull.Value));




            #endregion

            try
            {
                LMobileDatabase.ExecuteSingleQueryWhithParameters(_Query.ToString(), connectionString, _ListParameters.ToArray());

                foreach (EInspectionImage i in result.Inspection.Images)
                {
                    LInspectionImage.SaveMobile(i, connectionString);
                }


                LNote.DeleteMobile(result, connectionString);
                foreach (ENote n in result.Notes)
                {
                    LNote.InsertMobile(n, result.Id, connectionString);
                }

            }
            catch (Exception ex)
            {

                throw ex;
            }



        }

        public static void DeleteMobile(EInspectionResult result, string connectionString)
        {
            LNote.DeleteMobile(result,connectionString);
            StringBuilder _Query = new StringBuilder("Delete tb_UtilityInspectionResults where UtilityInspectionResultId =@UtilityInspectionResultId ");
            List<SqlParameter> listParameters = new List<SqlParameter>();
            listParameters.Add(DBHelper.CreateIntParameter("@UtilityInspectionResultId",ParameterDirection.Input,false,result.Id));
            LMobileDatabase.ExecuteSingleQueryWhithParameters(_Query.ToString(),connectionString,listParameters.ToArray());
        }

        #endregion

        #endregion

        #region CRUD

        public static Int32 Save(EInspectionResult inspectionResult)
        {
            Int32 tmpMobileId = inspectionResult.Id;

            Int32 idInspectionResult = WebConsumer.Inspection.WInspectionResult.Save(inspectionResult.SeverId, inspectionResult.Inspection.ServerID, inspectionResult.InspectionType.Id, inspectionResult.Resultype.Id,
                                                              inspectionResult.LastInspection,inspectionResult.Comments,inspectionResult.IsElectrical);

            inspectionResult.Id = idInspectionResult;
            LNote.DeleteByInspectionResult(inspectionResult);
            foreach (ENote n in inspectionResult.Notes)
            {
                LNote.Save(n, inspectionResult);
            }
            inspectionResult.Id = tmpMobileId;
            return idInspectionResult;
                
        }

        public static void Delete(EInspectionResult inspectionResult)
        {
            WebConsumer.Inspection.WInspectionResult.Delete(inspectionResult.Id);
        }

        #endregion

        public static List<EInspectionResult> ParseDatatableInspectionResult(DataTable dtInspectionResult)
        {
            List<EInspectionResult> _ListResults = new List<EInspectionResult>();

            foreach (DataRow dr in dtInspectionResult.Rows)
            {
                EInspectionResult _result = new EInspectionResult();
                _result.Id = ConvertHelper.ToInt32(dr["Vendor_InspVisit_XmissionStructures_ResultId"]);
                _result.SeverId = ConvertHelper.ToInt32(dr["Vendor_InspVisit_XmissionStructures_ResultId"]);
                _result.Inspection = LInspection.GetInspectionById(ConvertHelper.ToInt32(dr["Vendor_InspVisit_XMissionStructure_PoleFk"]));
                _result.InspectionType = new EInspectionTypes(ConvertHelper.ToInt32(dr["Vendor_InspVisit_XmissionStructures_Inspection_TypeFk"]), ConvertHelper.Tostring(dr["Type_Name"]));
                _result.LastInspection = ConvertHelper.Tostring(dr["LastInspection"]);
                _result.Resultype = new EResultType(ConvertHelper.ToInt32(dr["Vendor_InspVisit_XmissionStructures_ResultTypeFk"]), ConvertHelper.Tostring(dr["Result_Name"]));
                _result.Notes = LNote.GetByResultId(_result.Id);
                _result.Comments = dr["Comments"].ToString();
                _result.IsElectrical = ConvertHelper.Tobool(dr["bElectrical"]);
                _result.Inspection.Result = _result;
                _ListResults.Add(_result);

            }
            return _ListResults;
        }

        public static EInspectionResult GetInspectionResult(Int32 inspectionResultId)
        {
            DataTable InspectionResultDatatable =
                WebConsumer.Inspection.WInspectionResult.GetInspectionResultById(inspectionResultId).Tables[0];
            return ParseDatatableInspectionResult(InspectionResultDatatable)[0];
        }

        public static EInspectionResult GetInspectionResultByInspection(Int32 inspectionId)
        {
            DataTable InspectionResultDatatable =
                WebConsumer.Inspection.WInspectionResult.GetInspectionResultByInspection(inspectionId).Tables[0];
            return ParseDatatableInspectionResult(InspectionResultDatatable)[0];
        }

        public static List<EInspectionResult> FindInpectionsBetweenCoordinates(List<EInspectionResult> inspectionResults,double maxLongitude, double maxLatitude, double minLongitude, double minLatiude)
        {
            List<EInspectionResult> returnInspections = new List<EInspectionResult>();

            foreach (EInspectionResult insp in inspectionResults)
            {
                if (insp.Inspection.Latitude <= maxLatitude && insp.Inspection.Latitude >= minLatiude && insp.Inspection.Longitude >= minLongitude && insp.Inspection.Longitude <= maxLongitude)
                    returnInspections.Add(insp);
            }
            return returnInspections;


        }

        public static List<EInspectionResult> MatchingVendors(List<EInspectionResult> inspectionResults, List<EVendor> vendors)
        {
            List<EInspectionResult> matchingVisits = new List<EInspectionResult>();
            foreach (EInspectionResult v in inspectionResults)
            {
                foreach (EVendor vendor in vendors)
                {
                    if (v.Inspection.Project.Vendor.Id == vendor.Id)
                        matchingVisits.Add(v);
                }
            }
            return matchingVisits;
        }

        public static List<EInspectionResult> MatchingContacts(List<EInspectionResult> inspectionResults, List<EVendor_Contact> contacts)
        {
            List<EInspectionResult> matchingVisits = new List<EInspectionResult>();
            foreach (EInspectionResult v in inspectionResults)
            {
                foreach (EVendor_Contact contact in contacts)
                {
                    if (v.Inspection.Contact.Id == contact.Id)
                        matchingVisits.Add(v);
                }
            }
            return matchingVisits;
        }

        public static List<EInspectionResult> MatchingInspectionType(List<EInspectionResult> inspectionResults, List<EInspectionTypes> types)
        {
            List<EInspectionResult> matchingVisits = new List<EInspectionResult>();
            foreach (EInspectionResult v in inspectionResults)
            {
                foreach (EInspectionTypes t in types)
                {
                    if (v.InspectionType.Id == t.Id)
                        matchingVisits.Add(v);
                }
            }
            return matchingVisits;
        }

        public static List<EInspectionResult> MatchingResultType(List<EInspectionResult> inspectionResults, List<EResultType> types)
        {
            List<EInspectionResult> matchingVisits = new List<EInspectionResult>();
            foreach (EInspectionResult v in inspectionResults)
            {
                foreach (EResultType t in types)
                {
                    if (v.Resultype.Id == t.Id)
                        matchingVisits.Add(v);
                }
            }
            return matchingVisits;
        }

        public static List<EInspectionResult> MatchingProjects(List<EInspectionResult> inspectionResults, List<EVendor_Project> projects)
        {
            List<EInspectionResult> matchingVisits = new List<EInspectionResult>();
            foreach (EInspectionResult v in inspectionResults)
            {
                foreach (EVendor_Project project in projects)
                {
                    if (v.Inspection.Project.Id == project.Id)
                        matchingVisits.Add(v);
                }
            }
            return matchingVisits;
        }

    }
}
